package org.agilelang.db.sync;

/*
 AgileLang - A fourth generation language(http://www.agilelang.org).

 Copyright (C) 2013 Roderick Cusirramos (roderick20@hotmail.com)

 This program is free software; you can redistribute it and/or modify
 it under the terms of the GNU General Public License as published by
 the Free Software Foundation; either version 2 of the License, or
 (at your option) any later version.

 This program is distributed in the hope that it will be useful,
 but WITHOUT ANY WARRANTY; without even the implied warranty of
 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 GNU General Public License for more details.

 You should have received a copy of the GNU General Public License
 along with this program; if not, write to the Free Software
 Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Reader;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import javax.swing.JFrame;
import javax.swing.tree.DefaultMutableTreeNode;
import javax.swing.tree.DefaultTreeModel;
import javax.swing.tree.MutableTreeNode;
import javax.swing.tree.TreeCellRenderer;
import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBException;
import javax.xml.bind.Unmarshaller;
import org.agilelang.object.Model;
import org.agilelang.object.Project;
import org.agilelang.object.attributes.Attribute;

public final class DlgSync extends javax.swing.JDialog {

    /**
     * Creates new form DlgSync
     */
    MutableTreeNode root_local = new DefaultMutableTreeNode(new NodeData("head", "Model", "", ""));
    final DefaultTreeModel model_local = new DefaultTreeModel(root_local);
    MutableTreeNode root_table = new DefaultMutableTreeNode("Table");
    final DefaultTreeModel model_table = new DefaultTreeModel(root_table);
    List<Model> models_m;
    List<Model> models_t;
    HashMap dataSqlMysql = new HashMap();
    HashMap data = new HashMap();
    String query = "";
    String database = "", user = "", password = "", path_xml = "";
    Project project;

    public DlgSync(java.awt.Frame parent, boolean modal, String database, String user, String password, String port, String path_xml) {
        super(parent, modal);
        this.database = database;
        this.user = user;
        this.password = password;
        this.path_xml = path_xml;

        initComponents();
        dataSqlMysql.put("string", "VARCHAR");
        dataSqlMysql.put("date", "DATE");
        dataSqlMysql.put("text", "TEXT");
        dataSqlMysql.put("INT UNSIGNED", "INT UNSIGNED");
        dataSqlMysql.put("intu", "INT UNSIGNED");
        dataSqlMysql.put("int", "INT");
        dataSqlMysql.put("integer", "INT UNSIGNED");

        data.put("binary", "blob");
        data.put("boolean", "tinyint(1)");
        data.put("date", "date");
        data.put("datetime", "datetime");
        data.put("decimal", "decimal");
        data.put("float", "float");
        data.put("double", "double");
        data.put("integer", "int(11)");
        data.put("string", "varchar(255)");
        data.put("text", "text");
        data.put("time", "time");
        data.put("timestamp", "datetime");
        data.put("primary key", "`id` int(11) unsigned NOT NULL auto_increment");
        data.put("INT UNSIGNED", " int(11) unsigned NOT NULL auto_increment");
        data.put("int", "int(11)");

        TreeCellRenderer renderer = new CellRenderer();
        this.treeModels.setCellRenderer(renderer);



        loadModels();
        readXml();
        readTables();

        //System.out.println(query);
    }

    public DlgSync(java.awt.Frame parent, boolean modal, String path_xml) {
        super(parent, modal);
        
        project = readProject(path_xml) ;
        
        this.database = project.application.database; 
         this.user = project.application.login;
         this.password = project.application.password; 
         this.path_xml = path_xml;

        initComponents();
        dataSqlMysql.put("string", "VARCHAR");
        dataSqlMysql.put("date", "DATE");
        dataSqlMysql.put("text", "TEXT");
        dataSqlMysql.put("INT UNSIGNED", "INT UNSIGNED");
        dataSqlMysql.put("intu", "INT UNSIGNED");
        dataSqlMysql.put("int", "INT");
        dataSqlMysql.put("integer", "INT UNSIGNED");

        data.put("binary", "blob");
        data.put("boolean", "tinyint(1)");
        data.put("date", "date");
        data.put("datetime", "datetime");
        data.put("decimal", "decimal");
        data.put("float", "float");
        data.put("double", "double");
        data.put("integer", "int(11)");
        data.put("string", "varchar(255)");
        data.put("text", "text");
        data.put("time", "time");
        data.put("timestamp", "datetime");
        data.put("primary key", "`id` int(11) unsigned NOT NULL auto_increment");
        data.put("INT UNSIGNED", " int(11) unsigned NOT NULL auto_increment");
        data.put("int", "int(11)");

        TreeCellRenderer renderer = new CellRenderer();
        this.treeModels.setCellRenderer(renderer);



        loadModels();
        readXml();
        readTables();

        //System.out.println(query);
    }

    /**
     * This method is called from within the constructor to initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is always
     * regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        btnSync = new javax.swing.JButton();
        btnCancel = new javax.swing.JButton();
        jPanel1 = new javax.swing.JPanel();
        jScrollPane1 = new javax.swing.JScrollPane();
        treeModels = new javax.swing.JTree();
        jPanel2 = new javax.swing.JPanel();
        jScrollPane2 = new javax.swing.JScrollPane();
        treeTables = new javax.swing.JTree();

        setDefaultCloseOperation(javax.swing.WindowConstants.DISPOSE_ON_CLOSE);

        btnSync.setText("Sync");
        btnSync.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnSyncActionPerformed(evt);
            }
        });

        btnCancel.setText("Cancel");

        jPanel1.setBorder(javax.swing.BorderFactory.createTitledBorder("Models"));

        treeModels.setModel(model_local);
        jScrollPane1.setViewportView(treeModels);

        javax.swing.GroupLayout jPanel1Layout = new javax.swing.GroupLayout(jPanel1);
        jPanel1.setLayout(jPanel1Layout);
        jPanel1Layout.setHorizontalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 412, Short.MAX_VALUE)
                .addContainerGap())
        );
        jPanel1Layout.setVerticalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jScrollPane1)
                .addContainerGap())
        );

        jPanel2.setBorder(javax.swing.BorderFactory.createTitledBorder("Data Base"));
        jPanel2.setPreferredSize(new java.awt.Dimension(339, 273));

        treeTables.setModel(model_table);
        jScrollPane2.setViewportView(treeTables);

        javax.swing.GroupLayout jPanel2Layout = new javax.swing.GroupLayout(jPanel2);
        jPanel2.setLayout(jPanel2Layout);
        jPanel2Layout.setHorizontalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel2Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jScrollPane2, javax.swing.GroupLayout.DEFAULT_SIZE, 400, Short.MAX_VALUE)
                .addContainerGap())
        );
        jPanel2Layout.setVerticalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel2Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jScrollPane2, javax.swing.GroupLayout.DEFAULT_SIZE, 497, Short.MAX_VALUE)
                .addContainerGap())
        );

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addComponent(btnSync)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                        .addComponent(btnCancel))
                    .addGroup(layout.createSequentialGroup()
                        .addComponent(jPanel1, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                        .addComponent(jPanel2, javax.swing.GroupLayout.PREFERRED_SIZE, 432, javax.swing.GroupLayout.PREFERRED_SIZE)))
                .addGap(26, 26, 26))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jPanel1, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                    .addComponent(jPanel2, javax.swing.GroupLayout.DEFAULT_SIZE, 542, Short.MAX_VALUE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(btnSync)
                    .addComponent(btnCancel))
                .addContainerGap())
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void btnSyncActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnSyncActionPerformed
        DLGQuery dlg = new DLGQuery(new JFrame(), true, this.database, this.user, this.password);
        dlg.setQuery(query);
        dlg.setLocationRelativeTo(null);
        dlg.setVisible(true);

    }//GEN-LAST:event_btnSyncActionPerformed

    public void readXml() {
        int flag = 0;
        int t = models_m.size();
        for (int x = 0; x < t; x++) {
            Model model = models_m.get(x);
            MutableTreeNode node;
            if (this.searchTable(model.table)) {

                node = new DefaultMutableTreeNode(new NodeData("table", model.name, model.table, "old"));
            } else {
                node = new DefaultMutableTreeNode(new NodeData("table", model.name, model.table, "new"));
                flag = 1;
                query += "CREATE TABLE `" + model.table + "` (\n";
            }
            model_local.insertNodeInto(node, root_local, 0);
            List<Attribute> attrs = model.attributes;
            Attribute at = new Attribute();
            at.name = "id";
            at.type = "INT UNSIGNED";
            attrs.add(at);

            Collections.sort(attrs, new Comparator<Attribute>() {
                public int compare(Attribute s1, Attribute s2) {
                    if (s1.name.equals("id")) {
                        return 1;
                    } else {
                        return s1.name.compareToIgnoreCase(s2.name);
                    }
                }
            });
            int t2 = attrs.size();
            for (int y = 0; y < t2; y++) {
                Attribute attr = attrs.get(y);
                MutableTreeNode node_attr = null;
                if (searchTableAtrr(model.table, attr.name, attr.type) == 0) {
                    node_attr = new DefaultMutableTreeNode(new NodeData("column", attr.name, attr.type, "new"));
                    if (flag == 0) {
                        query += "ALTER TABLE " + model.table + " ADD " + attr.name + " " + data.get(attr.type) + ";\n";
                    }
                }
                if (searchTableAtrr(model.table, attr.name, attr.type) == 1) {
                    node_attr = new DefaultMutableTreeNode(new NodeData("column", attr.name, attr.type, "old"));
                }
                if (searchTableAtrr(model.table, attr.name, attr.type) == 2) {
                    node_attr = new DefaultMutableTreeNode(new NodeData("column", attr.name, attr.type, "modify"));
                    if (flag == 0) {
                        query += "ALTER TABLE " + model.table + " MODIFY " + attr.name + " " + data.get(attr.type) + ";\n";
                    }
                }
                if (flag == 1) {
                    query += "\t`" + attr.name + "` " + data.get(attr.type) + ",\n";
                }
                model_local.insertNodeInto(node_attr, node, 0);
            }
            if (flag == 1) {
                query += "PRIMARY KEY (`id`) );\n";
                flag = 0;
            }

        }
        for (int i = 0; i < treeModels.getRowCount(); i++) {
            treeModels.expandRow(i);
        }

    }

    public Project readProject(String path_xml) {
        File file = new File(path_xml);
        if (!file.exists()) {
            System.err.println("Fichero XML no existe");
            return null;
        }
        //leer xml
        InputStream is = null;
        Reader reader = null;
        try {
            is = new FileInputStream(path_xml);
            reader = new BufferedReader(new InputStreamReader(is, "UTF-8"));
            System.out.println("Reding xml...");
        } catch (Exception ex) {
            System.err.println("Error al leer fichero XML : " + ex);
        }
        //Poblar objecto Project        
        JAXBContext jc;
        Project project = null;
        try {
            jc = JAXBContext.newInstance(Project.class);
            Unmarshaller u = jc.createUnmarshaller();
            project = (Project) u.unmarshal(reader);
            System.out.println("Built project...");
        } catch (JAXBException ex) {
            System.err.println("Error al poblar objeto Project : " + ex.toString());
        }
        return project;
    }

    private void readTables() {

        int t = models_t.size();

        for (int x = 0; x < t; x++) {
            Model model = models_t.get(x);
            MutableTreeNode node = new DefaultMutableTreeNode(model.table);
            model_table.insertNodeInto(node, root_table, 0);
            List<Attribute> attrs = model.attributes;

            Collections.sort(attrs, new Comparator<Attribute>() {
                @Override
                public int compare(Attribute s1, Attribute s2) {
                    if (s2.name.equals("id")) {
                        return -1;
                    } else {
                        return s1.name.compareToIgnoreCase(s2.name);
                    }
                }
            });
            int t2 = attrs.size();
            for (int y = 0; y < t2; y++) {
                Attribute attr = attrs.get(y);
                MutableTreeNode node_attr = new DefaultMutableTreeNode("Column( name = \"" + attr.name + "\", type = \"" + attr.type + "\")");
                model_table.insertNodeInto(node_attr, node, 0);
            }
        }
        for (int i = 0; i < treeTables.getRowCount(); i++) {
            treeTables.expandRow(i);
        }
    }

    private void loadModels() {
        //Project project = readProject(path_xml);
        models_m = project.models;
        Collections.sort(models_m, new Comparator<Model>() {
            public int compare(Model s1, Model s2) {
                return s2.table.compareToIgnoreCase(s1.table);
            }
        });
        //-----------------------------------------------------------------------
        ReadDBMysql read = new ReadDBMysql(this.database, this.user, this.password);
        models_t = read.getListTables();

        Collections.sort(models_t, new Comparator<Model>() {
            @Override
            public int compare(Model s1, Model s2) {
                return (s1.table.compareToIgnoreCase(s2.table)) * (-1);
            }
        });
    }

    private boolean searchTable(String table) {
        int t = models_t.size();
        for (int x = 0; x < t; x++) {
            Model model = models_t.get(x);
            if (model.table.equals(table)) {
                return true;
            }
        }
        return false;
    }

    private int searchTableAtrr(String table, String attr_name, String attr_type) {
        int t = models_t.size();
        for (int x = 0; x < t; x++) {
            Model model = models_t.get(x);
            if (model.table.equals(table)) {
                List<Attribute> attrs = model.attributes;
                int t2 = attrs.size();
                for (int y = 0; y < t2; y++) {
                    Attribute attr = attrs.get(y);
                    if (attr.name.equals(attr_name)) {
                        if (attr.type.equals(dataSqlMysql.get(attr_type))) {
                            return 1;
                        } else {
                            return 2;
                        }
                    }
                }
            }
        }
        return 0;
    }
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton btnCancel;
    private javax.swing.JButton btnSync;
    private javax.swing.JPanel jPanel1;
    private javax.swing.JPanel jPanel2;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JScrollPane jScrollPane2;
    private javax.swing.JTree treeModels;
    private javax.swing.JTree treeTables;
    // End of variables declaration//GEN-END:variables
}
